﻿using Net.Pro.Models;
using NORM.DataBase;
using NORM.Entity;
using NORM.Models;
using NORM.SQLObject;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace NORM.Demo
{
    public partial class SQL测试继续 : Form
    {
        public SQL测试继续()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                string url = "Server=192.168.0.116;Initial Catalog=monthdbtester;User ID=sa;Pwd=YHPych920,.;";
                using (var db = NORM.DataBase.DataBaseFactory.Create(url, DataBase.DataBaseTypes.SqlDataBase))
                {
                    Net.Pro.Models.MeasureValues model = new Net.Pro.Models.MeasureValues();
                    OQL oql = OQL.From(model).Where(w => w.Compare(model.Datetime, ">=", "2016-07-15 09:10:00")
                        & w.Compare(model.Datetime, "<=", "2016-07-15 09:30:00")
                        & w.Compare(model.NodeID, "=", "490")).Select().End;//.Limit(1).End;
                    //.OrderBy(o => o.Asc("NodeID")).End;
                    var list = EntityQuery<Net.Pro.Models.MeasureValues>.Instance.QueryToList(oql, db);

                    var mod = EntityQuery<Net.Pro.Models.MeasureValues>.Instance.QueryToObject(oql, db);

                    MWaveData model2 = new MWaveData();
                    OQL oql2 = OQL.From(model2).Where(w => w.Compare(model2.DateTime, ">=", "2016-07-15 09:10:00")).Select().Limit(2).End;
                    var list2 = EntityQuery<Net.Pro.Models.MWaveData>.Instance.QueryToList(oql2, db);

                    var list3 = EntityQuery<Net.Pro.Models.MWaveData>.Instance.QueryToList(oql2, db);

                    List<Condition> conditions = new List<Condition>();
                    List<string> orderBy = new List<string>();
                    orderBy.Add("NodeID asc");

                    conditions.Add(new Condition() { Field = "Datetime", Comparison = ">=", Value = "2016-12-01" });
                    conditions.Add(new Condition() { Field = "Datetime", Comparison = "<=", Value = "2017-08-20" });

                    PageLimit pager = new PageLimit();
                    pager.PageSize = 100;
                    pager.PageIndex = 1;

                    OQL oql4 = OQL.From(model).Where(conditions.ToArray()).OrderBy(orderBy.ToArray()).Select(new string[] { 
                  "NodeID",
                  "Datetime",
                  "ParaID",
                  "SampleFreq",
                  "SampleLength",
                  "Ave",
                  "RMS",
                  "Vpp",
                  "AlarmType"
                }).End;


                    DataTable dt = db.QueryTable(oql4, pager);

                    var list4 = EntityQuery<Net.Pro.Models.MeasureValues>.Instance.QueryToList(oql4, pager, db);

                    string sql = "SELECT TOP 100 [NodeID] ,[Datetime] ,[ParaID] ,[SampleFreq] ,[SampleLength] ,[Ave] ,[RMS] ,[Vpp] ,[p1X] ,[Phase1X] ,[Mean] ,[WaveFormFactor] ,[WaveImpactFactor] ,[WaveKurtosis] ,[WaveSkewness] ,[WaveRMSAMP] ,[WaveYudu] ,[AmpUnit] ,[AngelUnit] ,[ConditionValue] ,[AlarmState] ,[AlarmType] ,[AlarmValue1] ,[AlarmValue2] ,[SpeedStamp] ,[ConditionValue1] ,[ConditionValue2] ,[ConditionValue3] ,[ConditionValue4] ,[ConditionValue5] ,[ConditionValue6] ,[ConditionValue7] ,[ConditionValue8] FROM [monthdb2tester].[dbo].[MeasureValues]";
                    var dd = db.QueryTable(sql);

                    OsqlParameter[] paramters ={
                          new OsqlParameter ("@PageIndex",OsqlParameterDataType.Int,4), 
                          new OsqlParameter ("@PageSize",OsqlParameterDataType.Int,4), 
                          new OsqlParameter ("@Key",OsqlParameterDataType.VarChar,50),
                          new OsqlParameter ("@Sql",OsqlParameterDataType.VarChar),
                          new OsqlParameter ("@TotalRecord",OsqlParameterDataType.Int,100)
                                                               };

                    paramters[0].Value = 1;
                    paramters[1].Value = 20;
                    paramters[2].Value = "Datetime asc";
                    paramters[3].Value = sql;
                    paramters[4].Direction = System.Data.ParameterDirection.Output;                     

                    var dc = db.QueryDataSet(CommandType.StoredProcedure, "usp_GetPageData", paramters);

                    var o = paramters[4].Value;

                    MessageBox.Show("ok");

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            using (var db = NORM.DataBase.DataBaseFactory.Create("Server=192.168.0.116;Initial Catalog=CMSGroupSet;User ID=sa;Pwd=YHPych920,.;", NORM.DataBase.DataBaseTypes.SqlDataBase))
            {
                db.BeginTransaction();
                try
                {
                    BearingFactory model1 = new BearingFactory();
                    model1.FactoryID = 0;
                    model1.FactoryName = "www";
                    model1.Remark = "sss";
                    db.Insert<Models.BearingFactory>(model1);
                    EntityQuery<Models.BearingFactory>.Instance.Insert(model1, db);
                    db.Commit();

                    MessageBox.Show("ok");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    db.RollBack();
                    //throw ex;
                }              

            }
        }
    }
}
